﻿CREATE VIEW reports.v_DemographicsCompare
AS
SELECT     CE.Name, BH.FirstName, CE.NameFirst, CASE WHEN dbo.StringMatch(BH.FirstName, CE.NameFirst) < 50 THEN 1 ELSE 0 END AS FN_X, 
                      BH.LastName, CE.NameLast, CASE WHEN BH.LastName <> CE.NameLast THEN 1 ELSE 0 END AS LN_X, BH.SSN, CE.[Primary SSN], 
                      CASE WHEN CE.[Primary SSN] <> CE.SSN THEN 1 ELSE 0 END AS SSN_X, BH.PhoneHome, CE.[Home Phone #], 
                      CASE WHEN CE.[Home Phone #] <> dbo.FormatPhone(BH.PhoneHome) THEN 1 ELSE 0 END AS Phone_X, BH.Street, CE.Address, 
                      dbo.StringMatch(BH.Street, CE.Address) AS StreetMatch, CASE WHEN dbo.StringMatch(BH.Street, CE.Address) < 50 THEN 1 ELSE 0 END AS Street_X, 
                      BH.CityName, CE.City, CASE WHEN BH.CityName <> CE.City THEN 1 ELSE 0 END AS City_X, BH.StateCode, CE.St, 
                      CASE WHEN BH.StateCode <> CE.St THEN 1 ELSE 0 END AS State_X, BH.Zip, CE.[Zip Code], 
                      CASE WHEN CE.[Zip Code] <> BH.Zip THEN 1 ELSE 0 END AS Zip_X, BH.VIN, CE.Serial#, BH.B_DateStamp, BH.B_UserNane, BH.C_DateStamp, 
                      BH.C_UserName, BH.A_DateStamp, BH.A_UserName, CE.[Status Code], CE.[Customer #], CE.CompanyID, BH.LoanID
FROM         reports.v_BorrowerHistory AS BH INNER JOIN
                      usix.t_CustomerFile_Export AS CE ON BH.LoanID = CE.LoanID
WHERE     (dbo.StringMatch(BH.FirstName, CE.NameFirst) < 50) AND (CE.[Status Code] IN ('O', 'L')) OR
                      (CE.[Status Code] IN ('O', 'L')) AND (BH.LastName <> CE.NameLast) OR
                      (CE.[Status Code] IN ('O', 'L')) AND (CE.[Primary SSN] <> CE.SSN) OR
                      (CE.[Status Code] IN ('O', 'L')) AND (dbo.clr_RegEx_Replace(CE.[Home Phone #], N'\D', N'', - 1, 0, 1) <> dbo.clr_RegEx_Replace(BH.PhoneHome, N'\D', 
                      N'', - 1, 0, 1)) OR
                      (CE.[Status Code] IN ('O', 'L')) AND (dbo.StringMatch(BH.Street, CE.Address) < 50) OR
                      (CE.[Status Code] IN ('O', 'L')) AND (BH.CityName <> CE.City) OR
                      (CE.[Status Code] IN ('O', 'L')) AND (BH.StateCode <> CE.St) OR
                      (CE.[Status Code] IN ('O', 'L')) AND (CE.[Zip Code] <> BH.Zip) OR
                      (CE.[Status Code] IN ('O', 'L')) AND (CE.Serial# <> CE.VIN)

GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane1', @value = N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[22] 4[35] 2[27] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4[37] 2[17] 3) )"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "BH"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 337
               Right = 189
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "CE"
            Begin Extent = 
               Top = 6
               Left = 227
               Bottom = 278
               Right = 426
            End
            DisplayFlags = 280
            TopColumn = 58
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 35
         Width = 284
         Width = 1950
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 3660
         Width = 3960
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 2070
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 17
         Column = 5475
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 345
         SortOrder = 600
         GroupBy = 1350
         Filter = 615
         Or', @level0type = N'SCHEMA', @level0name = N'reports', @level1type = N'VIEW', @level1name = N'v_DemographicsCompare';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane2', @value = N'= 795
         Or = 660
         Or = 675
         Or = 645
         Or = 675
         Or = 1020
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End', @level0type = N'SCHEMA', @level0name = N'reports', @level1type = N'VIEW', @level1name = N'v_DemographicsCompare';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPaneCount', @value = 2, @level0type = N'SCHEMA', @level0name = N'reports', @level1type = N'VIEW', @level1name = N'v_DemographicsCompare';

